Hi.
In this blog I will teach you that how to execute your first query by using SqlCommand object and display record by using SqlDataReader object. In this program, I am using three class one is SqlConnection class which establish the connection from SQL server, second one is SqlCommand class which store query information and is used to execute the query and last one is SqlDataReader object which store result set which contains all records of query.
Program which is used to execute sql query for select command
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; //Namespace required for ado.net programing.
using System.Data.SqlClient; //Namespace required for ado.net programing.
namespace ConnectionDemo
{
class Program
{
/// <summary>
/// This property will return connection string required to establish connection to desired server.
/// </summary>
public static string getConnectionString
{
get
{
return @"Data Source=AA-PC;User Id=sa;Password=aaaaa;Initial Catalog=WorkBook";
}
}
/// <summary>
/// This method will return boolean type value. If it returns true it means connection successfully
/// establish and if it returns false then it means some problem occurs while establishing connection to server.
/// </summary>
/// <returns></returns>
public static bool establishConnection()
{
bool status = false;
try
{
con = new SqlConnection(); //Create an object of SqlConnection class. This class will reside in System.Data.SqlClient namespace.
con.ConnectionString = getConnectionString; //Pass connection string to SqlConnection class by calling ConnectionString property of SqlConnection class.
con.Open(); //Open connection by calling Open() method of SqlConnection class.
status = true; //Make status to be true flag.
}
catch
{
status = false; //If any exception is generated then make status to be false. This means connection is not established.
}
return status;
}
private static SqlConnection con = null; //Create a reference variable of SqlConnection class.
private static SqlCommand cmd = null; //Create a reference variable of SqlCommand class.
private static SqlDataReader dr = null; //Create a reference variable of SqlDataReader class.
/// <summary>
/// This method will display record from database depends upon query.
/// </summary>
public static void displayRecordFromDatabase(string query)
{
if (establishConnection())
{
if (con != null)
{
try
{
cmd = con.CreateCommand(); //Create an object of sqlcommand class.
cmd.CommandText = query; //Pass command text to SqlCommand object.
cmd.CommandType = CommandType.Text; //Tell command type to text.
dr = cmd.ExecuteReader(); //Execute sql command by calling execute reader method. This will return DataReader object which will store all rows from table.
//Repeate statement untill rows are remaining in database.
while (dr.Read())
{
//Field count property will return total number of columns in a row.
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + "\t");
}
Console.WriteLine();
}
}
catch
{
Console.WriteLine("Invalid query. Please enter valid query.");
}
finally
{
if (dr != null)
{
dr.Close(); //Close data reader object.
}
if (con != null)
con.Close(); //Close sqlconnection object.
}
}
}
else
Console.WriteLine("Unable to establish connection.");
}
static void Main(string[] args)
{
string query = String.Empty; //Initilize empty value in string variable.
Console.WriteLine("Please eneter sqlquery for select record.."); //Display a message to user.
query = Console.ReadLine(); //Read input from user.
while (!string.IsNullOrEmpty(query))
{
displayRecordFromDatabase(query); //Call displayRecordFromDatabase() method.
Console.ReadLine(); //Wait for user.
Console.Clear(); //Clear console window.
Console.WriteLine("Please eneter sqlquery for select record.."); //Again display message.
query = Console.ReadLine(); //Read input from user.
}
}
}
}
Output of following code snippet is as follows
Please eneter sqlquery for select record..
select * from product
P0001 AAAA 23
P0002 BBBB 23
P0003 BBBB 23
P0004 AAAA 23
P0005 AAAA 23
P0006 BBBB 23
Please eneter sqlquery for select record..
select * product
Invalid query. Please enter valid query.
Anonymous User
06-Mar-2019Thank you for the informative post.
Sunil Singh
10-Jul-2017It was really helpful to read this post.